﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SMS.DAL.DAHelpers;
using SMS.Entities;
using System.Data;
using SMS.Entities.BaseClasses;
using SMS.Entities.Report;

namespace SMS.DAL
{
    public class ReportDA : BaseDA<SaleDetail>
    {

        public override SaleDetail SaveObject(string userName, SaleDetail tObject)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteObject(string userName, SaleDetail tObject)
        {
            throw new NotImplementedException();
        }

        public override List<SaleDetail> GetObjectList(string userName, DateTime fromDate, DateTime toDate)
        {
            throw new NotImplementedException();
        }

        public override List<SaleDetail> SearchObject(string userName, string SearchText)
        {
            throw new NotImplementedException();
        }

        #region "Sales Report Related Methods"
        public List<SaleReport> GetSalesReportDataList(string username, DateTime fromDate, DateTime toDate,string SearchOption)
        {
            /*ALTER PROCEDURE [dbo].[USP_SEARCH_SALES_REPORT]-- '2011-01-01','2012-12-30','DAILY'
(
@FROMDATE DATETIME,
@TODATE DATETIME,
@SearchBy varchar(50)
)
AS

SELECT 
        DBO.RETURNNOBYDATE(H.selling_Date,UPPER(@SearchBy)) AS 'No'
        ,datepart(year,H.selling_Date) as 'Year'
        ,sum(D.[Quantity]) as 'Quantity'
        ,sum(D.[TotalAmount]) as 'Unit_Price'
        ,sum(D.[BilledAmount]) as 'Billed_Amount'
        ,sum(p.buyingPrice*d.quantity) as 'Buying Price'
        ,sum(D.[BilledAmount])-sum(p.buyingPrice*d.quantity) as 'Margin'
        ,sum(D.[TotalAmount])-sum(D.[BilledAmount] )  as 'Discount'   
		
     FROM SALEDETAIL D inner join SALEHISTORY H ON D.SALES_ID=H.SALE_ID inner join ProductStockWise p on d.productstockwiseid=p.product_stock_id
     WHERE H.SELLING_DATE> @FROMDATE AND H.SELLING_DATE<@TODATE group by DBO.RETURNNOBYDATE(H.selling_Date,UPPER(@SearchBy)),datepart(year,H.selling_Date)
*/
            try
            {
                dbHelper.AddParameter("@FROMDATE", fromDate);
                dbHelper.AddParameter("@TODATE", toDate);
                dbHelper.AddParameter("@SearchBy", SearchOption);
                IDataReader dr = dbHelper.ExecuteReader("USP_SEARCH_SALES_REPORT", CommandType.StoredProcedure, ConnectionState.Closed);
                var list = new List<SaleReport>();
                while (dr.Read())
                {
                    list.Add(GetSaleDetailObject(dr, false, SearchOption));
                }
                dr.Close();
                LogActivity(username, string.Format("Sales Report loaded from {0} to {1}", fromDate, toDate), null);
                return list;
            }
            catch (Exception ex)
            {
                LogException(username, ex, null);
                throw;
            }
        }

        private SaleReport GetSaleDetailObject(IDataReader reader, bool p, string SearchOption)
        {
            SaleReport detail = new SaleReport();
            int i = -1;
            if (!reader.IsDBNull(++i))
                detail.GroupNo = reader.GetInt32(i);
            if (!reader.IsDBNull(++i))
                detail.Year = reader.GetInt32(i);
            if (!reader.IsDBNull(++i))
                detail.Quantity = reader.GetInt32(i);
            if (!reader.IsDBNull(++i))
                detail.SellingAmount = reader.GetDecimal(i);
            if (!reader.IsDBNull(++i))
                detail.BilledAmount = reader.GetDecimal(i);
            if (!reader.IsDBNull(++i))
                detail.BuyingAmount = reader.GetDecimal(i);
            if (!reader.IsDBNull(++i))
                detail.Margin = reader.GetDecimal(i);
            if (!reader.IsDBNull(++i))
                detail.Discount = reader.GetDecimal(i);
            return detail;
        }

        #endregion        

        #region "Product Sales Report Related Methods"

        public List<ProductSaleReport> GetProductSaleReportDataList(string username, DateTime fromDate, DateTime toDate, string SearchOption)
        {
            /*ALTER PROCEDURE [dbo].[USP_PRODUCTSALES_REPORT] --2,'2011-01-01','2012-11-11','DAILY'
(
@FROMDATE DATETIME,
@TODATE DATETIME,
@SearchBy varchar(50)
)
AS

SELECT 
		DBO.RETURNNOBYDATE(H.selling_Date,UPPER(@SearchBy)) AS 'No'
		,datepart(year,H.selling_Date) as 'Year'
		,PRO.PRODUCT_Name as 'ProductName' 
		,sum(D.Quantity) as 'TotalQuantity'	
		,sum(D.[SellingUnitPrice]) as 'TotalSellingPrice'
		,sum(D.[SellingUnitPrice])-sum(p.buyingPrice) as 'TotalIncome'				
     FROM SALEDETAIL D inner join SALEHISTORY H ON D.SALES_ID=H.SALE_ID inner join ProductStockWise p on d.productstockwiseid=p.product_stock_id     
     inner join PRODUCT PRO on P.PRODUCT_ID=PRO.Product_ID
     WHERE H.SELLING_DATE> @FROMDATE AND H.SELLING_DATE<@TODATE 
     group by DBO.RETURNNOBYDATE(H.selling_Date,UPPER(@SearchBy)),datepart(year,H.selling_Date),PRO.PRODUCT_Name
*/
            try
            {
                dbHelper.AddParameter("@FROMDATE", fromDate);
                dbHelper.AddParameter("@TODATE", toDate);
                dbHelper.AddParameter("@SearchBy", SearchOption);
                IDataReader dr = dbHelper.ExecuteReader("USP_PRODUCTSALES_REPORT", CommandType.StoredProcedure, ConnectionState.Closed);
                var list = new List<ProductSaleReport>();
                while (dr.Read())
                {
                    list.Add(GetProductSaleReportObject(dr, false, SearchOption));
                }
                dr.Close();
                LogActivity(username, string.Format("Customer Sales Report loaded from {0} to {1}", fromDate, toDate), null);
                return list;
            }
            catch (Exception ex)
            {
                LogException(username, ex, null);
                throw;
            }
        }

        private ProductSaleReport GetProductSaleReportObject(IDataReader reader, bool p, string SearchOption)
        {
            ProductSaleReport detail = new ProductSaleReport();
            int i = -1;
            if (!reader.IsDBNull(++i))
                detail.GroupNo = reader.GetInt32(i);
            if (!reader.IsDBNull(++i))
                detail.Year = reader.GetInt32(i);
            if (!reader.IsDBNull(++i))
                detail.ProductName = reader.GetString(i);
            if (!reader.IsDBNull(++i))
                detail.TotalQuantity = reader.GetInt32(i);
            if (!reader.IsDBNull(++i))
                detail.TotalSellingPrice = reader.GetDecimal(i);
            if (!reader.IsDBNull(++i))
                detail.TotalIncome = reader.GetDecimal(i);
            return detail;
        }


        #endregion

        #region "Customer Report Related Methods"
        public List<CustomerReport> GetCustomerReportDataList(string username, DateTime fromDate, DateTime toDate, string SearchOption)
        {
            /*ALTER PROCEDURE [dbo].[USP_CUSTOMER_REPORT] --'2011-01-01','2012-11-11','MONTHLY'
(
@FROMDATE DATETIME,
@TODATE DATETIME,
@SearchBy varchar(50)
)
AS

SELECT 
		DBO.RETURNNOBYDATE(H.selling_Date,UPPER(@SearchBy)) AS 'No'
		,datepart(year,H.selling_Date) as 'Year'
		,c.Customer_Name as 'CustomerName' 		
		,sum(D.[SellingUnitPrice]) as 'TotalSellingPrice'
		,sum(D.[SellingUnitPrice])-sum(p.buyingPrice) as 'TotalIncome'		
     FROM SALEDETAIL D inner join SALEHISTORY H ON D.SALES_ID=H.SALE_ID inner join ProductStockWise p on d.productstockwiseid=p.product_stock_id     
     inner join customer c on h.customer_id=c.customer_ID
     WHERE H.SELLING_DATE> @FROMDATE AND H.SELLING_DATE<@TODATE group by DBO.RETURNNOBYDATE(H.selling_Date,UPPER(@SearchBy)),datepart(year,H.selling_Date)
     ,c.Customer_Name
*/
            try
            {
                dbHelper.AddParameter("@FROMDATE", fromDate);
                dbHelper.AddParameter("@TODATE", toDate);
                dbHelper.AddParameter("@SearchBy", SearchOption);
                IDataReader dr = dbHelper.ExecuteReader("USP_CUSTOMER_REPORT", CommandType.StoredProcedure, ConnectionState.Closed);
                var list = new List<CustomerReport>();
                while (dr.Read())
                {
                    list.Add(GetCustomerReportObject(dr, false, SearchOption));
                }
                dr.Close();
                LogActivity(username, string.Format("Customer Report loaded from {0} to {1}", fromDate, toDate), null);
                return list;
            }
            catch (Exception ex)
            {
                LogException(username, ex, null);
                throw;
            }
        }

        private CustomerReport GetCustomerReportObject(IDataReader reader, bool p, string SearchOption)
        {
            CustomerReport report = new CustomerReport();            
            int i = -1;
            if (!reader.IsDBNull(++i))
                report.GroupNo = reader.GetInt32(i);
            if (!reader.IsDBNull(++i))
                report.Year = reader.GetInt32(i);
            if (!reader.IsDBNull(++i))
                report.CustomerName = reader.GetString(i);
            if (!reader.IsDBNull(++i))
                report.TotalSellingPrice = reader.GetDecimal(i);
            if (!reader.IsDBNull(++i))
                report.TotalIncome = reader.GetDecimal(i);
            return report;
        }

        #endregion

        #region "Individual Customer Report Related Methods"
        public List<CustomerReport> GetIndividualCustomerReportDataList(string username, int customerID, DateTime fromDate, DateTime toDate, string SearchOption)
        {
            /*ALTER PROCEDURE [dbo].[USP_INDIVIDUAL_CUSTOMER_REPORT] --'2011-01-01','2012-11-11','MONTHLY'
(
@CUSTOMERID INT,
@FROMDATE DATETIME,
@TODATE DATETIME,
@SearchBy varchar(50)
)
AS

SELECT 
		DBO.RETURNNOBYDATE(H.selling_Date,UPPER(@SearchBy)) AS 'No'
		,datepart(year,H.selling_Date) as 'Year'
		,c.Customer_Name as 'CustomerName' 		
		,sum(D.[SellingUnitPrice]) as 'TotalSellingPrice'
		,sum(D.[SellingUnitPrice])-sum(p.buyingPrice) as 'TotalIncome'		
     FROM SALEDETAIL D inner join SALEHISTORY H ON D.SALES_ID=H.SALE_ID inner join ProductStockWise p on d.productstockwiseid=p.product_stock_id     
     inner join customer c on h.customer_id=c.customer_ID
     WHERE C.customer_ID=@CUSTOMERID AND H.SELLING_DATE> @FROMDATE AND H.SELLING_DATE<@TODATE 
     group by DBO.RETURNNOBYDATE(H.selling_Date,UPPER(@SearchBy)),datepart(year,H.selling_Date),c.Customer_Name
*/
            try
            {
                dbHelper.AddParameter("@CUSTOMERID", customerID);
                dbHelper.AddParameter("@FROMDATE", fromDate);
                dbHelper.AddParameter("@TODATE", toDate);
                dbHelper.AddParameter("@SearchBy", SearchOption);
                IDataReader dr = dbHelper.ExecuteReader("USP_INDIVIDUAL_CUSTOMER_REPORT", CommandType.StoredProcedure, ConnectionState.Closed);
                var list = new List<CustomerReport>();
                while (dr.Read())
                {
                    list.Add(GetCustomerReportObject(dr, false, SearchOption));
                }
                dr.Close();
                LogActivity(username, string.Format("Individual Customer Report loaded from {0} to {1}", fromDate, toDate), null);
                return list;
            }
            catch (Exception ex)
            {
                LogException(username, ex, null);
                throw;
            }
        }
        #endregion

        #region "New Customer Report Related Methods"
        public List<NewCustomerReport> GetNewCustomerReportDataList(string username, DateTime fromDate, DateTime toDate, string SearchOption)
        {
            /*
            ALTER PROCEDURE [dbo].[USP_NEWCUSTOMER_REPORT] --'2011-01-01','2012-11-11','DAILY'
(
@FROMDATE DATETIME,
@TODATE DATETIME,
@SearchBy varchar(50)
)
AS

SELECT 
		DBO.RETURNNOBYDATE(Creation_Date,UPPER(@SearchBy)) AS 'No'
		,datepart(year,Creation_Date) as 'Year'		
		,COUNT(*) as 'TotalNew'			
		 FROM CUSTOMER C 
		 WHERE Creation_Date> @FROMDATE AND Creation_Date<@TODATE 
		 group by DBO.RETURNNOBYDATE(Creation_Date,UPPER(@SearchBy)),datepart(year,Creation_Date)
            */
            try
            {
                dbHelper.AddParameter("@FROMDATE", fromDate);
                dbHelper.AddParameter("@TODATE", toDate);
                dbHelper.AddParameter("@SearchBy", SearchOption);
                IDataReader dr = dbHelper.ExecuteReader("USP_NEWCUSTOMER_REPORT", CommandType.StoredProcedure, ConnectionState.Closed);
                var list = new List<NewCustomerReport>();
                while (dr.Read())
                {
                    list.Add(GetNewCustomerReportObject(dr, false, SearchOption));
                }
                dr.Close();
                LogActivity(username, string.Format("New Customer Report loaded from {0} to {1}", fromDate, toDate), null);
                return list;
            }
            catch (Exception ex)
            {
                LogException(username, ex, null);
                throw;
            }
        }

        private NewCustomerReport GetNewCustomerReportObject(IDataReader reader, bool p, string SearchOption)
        {
            NewCustomerReport report = new NewCustomerReport();
            int i = -1;
            if (!reader.IsDBNull(++i))
                report.GroupNo = reader.GetInt32(i);
            if (!reader.IsDBNull(++i))
                report.Year = reader.GetInt32(i);
            if (!reader.IsDBNull(++i))
                report.TotalNew = reader.GetInt32(i);            
            return report;
        }

        #endregion
    }
}
